﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class SiteChannelDAL
    {
        private const string Waming = "SqlServerDAL.SiteChannelDAL";

        public bool Save(SiteChannelDto dto)
        {
            try
            {
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@ChannelName", SqlDbType.Int) {Value = dto.ChannelName},
                                         new SqlParameter("@ChannelTemplateId", SqlDbType.Int)
                                             {Value = dto.ChannelTemplateId},
                                         new SqlParameter("@ChannelIndexImg", SqlDbType.VarChar)
                                             {Value = dto.ChannelIndexImg},
                                         new SqlParameter("@ChannelTime", SqlDbType.DateTime) {Value = dto.ChannelTime},
                                         new SqlParameter("@ChannelCode", SqlDbType.VarChar) {Value = dto.ChannelCode},
                                         new SqlParameter("@ChannelCurState", SqlDbType.Int)
                                             {Value = dto.ChannelCurState}
                                     };

                if (dto.ChannelId == 0)
                {
                    sql =
                        @"insert into SiteChannel(ChannelName,ChannelTemplateId,ChannelIndexImg,ChannelTime,ChannelCode,
                        ChannelCurState) values(@ChannelName,@ChannelTemplateId,@ChannelIndexImg,@ChannelTime,@ChannelCode,
                        @ChannelCurState)";
                }
                else
                {
                    sql =
                        @"update SiteChannel set ChannelName=@ChannelName,ChannelTemplateId=@ChannelTemplateId,
                        ChannelIndexImg=@ChannelIndexImg,ChannelTime=@ChannelTime,ChannelCode=@ChannelCode,
                        ChannelCurState=@ChannelCurState where ChannelId=@ChannelId";
                    parameters.Add(new SqlParameter("@ChannelId", SqlDbType.Int) { Value = dto.ChannelId });
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(SiteChannelDto dto)", ex);
                return false;
            }
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, string order)
        {
            try
            {
                var hts = new List<Hashtable>();
                order = string.IsNullOrEmpty(order) ? "order by ChannelId desc" : order;
                var sql = "select row_number() over (" + order + ") as rowNumber, * from SiteChannel";
                var whereSql = "";
                sql = string.Format(
                    "select * from (" + sql + whereSql +
                    ") as SiteChannel where rowNumber between {0} AND {1}",
                    firstResult, maxResult);
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SlicedFindAll(int firstResult, int maxResult, string order)", ex);
                return null;
            }
        }

        public int SelectCount()
        {
            try
            {
                const string sql = "select count(*) from SiteChannel";
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql);
                return Convert.ToInt32(red);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectCount()", ex);
                return -1;
            }
        }

        public Hashtable FindById(int id)
        {
            try
            {
                Hashtable ht = null;
                const string sql = "select * from SiteChannel where ChannelId=@ChannelId";
                var param = new SqlParameter("@ChannelId", SqlDbType.Int) { Value = id };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, param))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindById(int id)", ex);
                return null;
            }
        }

        public Hashtable FindOne(string code)
        {
            try
            {
                Hashtable ht = null;
                var parameters = new List<SqlParameter>();
                const string sql = "select * from SiteChannel";
                var whereSql = "";
                if (code != "")
                {
                    whereSql += " where ChannelCode=@ChannelCode";
                    parameters.Add(new SqlParameter("@ChannelCode", SqlDbType.Int) { Value = code });
                }

                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql + whereSql, parameters.ToArray()))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindOne(string code)", ex);
                return null;
            }
        }

        public List<Hashtable> FindAll(int curState)
        {
            try
            {
                var hts = new List<Hashtable>();
                const string sql = "select * from SiteChannel";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (curState != 0)
                {
                    whereSql += " where ChannelCurState=@ChannelCurState";
                    parameters.Add(new SqlParameter("@ChannelCurState", SqlDbType.Int) { Value = curState });
                }
                using (
                    var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                      parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindAll(int curState)", ex);
                return null;
            }
        }
    }
}
